<?php
namespace Home\Model;
use Think\Model;

class SyncModel extends Model{
	//商户查询
	public function shopQuery($t=null,$kind=null,$infor=null,$lat=null,$lon=null){
		//查询所以商户信息
		// $sql="select * from kl_buss buss LEFT JOIN kl_busstype type on buss.type=type.id ORDER BY buss.bs_id ASC;";
		// $info = $this->query($sql);
// 		//用户名存在用户名和传进来的密码比较
// 		if($info){
// 			if ($info['mg_pwd'] == $pwd) {
// 				return $info;
// 			}
// 		}

		//商家列表按距离排序
		if ($t=="1") {
			// $info = $kind;
			if (!empty($kind)) {
				if ($kind=='movie') {
					$type="电影";
				}
				else if ($kind=='food') {
					$type="美食";
				}
				$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss WHERE type='{$type}' ORDER BY dis asc;";
				$info = $this->query($sql);
				
			}else{
				$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss ORDER BY dis asc;";
				$info = $this->query($sql);
			}
			
		}

		//商家列表按热门排序
		if ($t=="2") {
			if (!empty($kind)) {
				if ($kind=='movie') {
					$type="电影";
				}
				else if ($kind=='food') {
					$type="美食";
				}
				$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss WHERE type='{$type}' ORDER BY score DESC;";
				$info = $this->query($sql);
				
			}else{
			//查询所有商户信息
			// select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss ORDER BY score DESC;
			// $sql="select * from kl_buss buss ORDER BY score DESC;";
				$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss ORDER BY score DESC;";
				$info = $this->query($sql);
			}
		}
		//按用户查询条件检索商家
		else if ($t=="3") {
			// $sql="select * from kl_buss buss  WHERE shopname like '%{$infor}%' ORDER BY score ASC ;";
			$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss WHERE shopname like '%{$infor}%' ORDER BY dis asc;";
			// select *,ACOS(SIN((119.33022111 * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS((119.33022111 * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS((26.0471255 * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138 as dis from kl_buss WHERE shopname like '%{$infor}%' ORDER BY dis asc;
			// return $this->query($sql);
			$info = $this->query($sql);

		}

		//检索商家1Km内的商家
		else if ($t=="4") {
			// $sql="select * from kl_buss buss  WHERE shopname like '%{$infor}%' ORDER BY score ASC ;";
			$sql="select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss WHERE ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138  ORDER BY dis asc;";
			// select *,ACOS(SIN((119.33022111 * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS((119.33022111 * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS((26.0471255 * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138 as dis from kl_buss WHERE shopname like '%{$infor}%' ORDER BY dis asc;
			// return $this->query($sql);
			$info = $this->query($sql);

		}

		return $info;
	}

	//获取商家详情
	public function details($shopid=null){
			// SELECT * FROM kl_buss buss LEFT JOIN kl_buss_ext ext on buss.bs_id=ext.buss  WHERE buss.bs_id=1
			$sql="SELECT * FROM kl_buss buss LEFT JOIN kl_buss_ext ext on buss.bs_id=ext.buss  WHERE buss.bs_id={$shopid};";
			// SELECT * FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss  WHERE buss.bs_id=1 AND "2016-4-10">pre.beg_time AND "2016-4-10"<pre.end_time
			// $now=date('Y-m-d');
			// $presql="SELECT pre.* FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss  WHERE buss.bs_id={$shopid} AND '{$now}'>pre.beg_time AND '{$now}'<pre.end_time;";
			$info = $this->query($sql);
		return $info;
	}

	//获取商家优惠券信息
	public function coupon($shopid=null){
		$now=date('Y-m-d');
		$sql="SELECT pre.* FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss  WHERE buss.bs_id={$shopid} AND '{$now}'>=pre.beg_time AND '{$now}'<=pre.end_time;";
		$info = $this->query($sql);
		// dump($sql);
		return $info;
	}

	//获取评论信息
	public function comment($shopid=null){
		$sql="SELECT * FROM kl_buss buss LEFT JOIN kl_user_sign sign on buss.bs_id=sign.buss  WHERE buss.bs_id={$shopid};";
		$info = $this->query($sql);
		return $info;
	}

	//获取签到信息
	public function signInfor($userid=null){
		// SELECT max(time) AS time FROM kl_user_sign sign WHERE user='b'
		$sql="SELECT max(time) AS time FROM kl_user_sign sign WHERE user='{$userid}';";
		$info = $this->query($sql);
		return $info;
	}

	//获取收藏信息
	public function ifCollection($shopid=null,$openid=null){
		// SELECT max(time) AS time FROM kl_user_sign sign WHERE user='b'
		$sql="SELECT * FROM kl_user_buss  WHERE buss='{$shopid}' and user='{$openid}';";
		$info = $this->query($sql);
		return $info;
	}

	//获取用户Token
	public function getToken($shopid=null){
		$sql="SELECT user FROM kl_buss buss LEFT JOIN kl_user_comment com on buss.bs_id=com.shopid  WHERE buss.bs_id={$shopid};";
		$info = $this->query($sql);
		return $info;
	}

	//获取用户表的openid
	public function getUser(){
		$sql="SELECT openid FROM kl_user user";
		$info = $this->query($sql);
		return $info;
	}

	//获取有优惠信息的商家信息
	public function getShop($lat=null,$lon=null){
		// select *,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis from kl_buss WHERE ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138  ORDER BY dis asc;
		// SELECT DISTINCT buss.bs_id,buss.* FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss WHERE pre.buss is not null
		$sql="SELECT DISTINCT buss.bs_id,buss.*,ROUND(ACOS(SIN(('{$lat}' * PI()) / 180 ) *SIN((lat * PI()) / 180 ) +COS(('{$lat}' * PI()) / 180 ) * COS((lat * PI()) / 180 ) *COS(('{$lon}' * PI()) / 180 - (lon * PI()) / 180 ) ) * 6378.138,3) as dis FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss WHERE pre.buss is not null ORDER BY dis asc;";
		$info = $this->query($sql);
		return $info;
	}

	//获取优惠信息
	public function getPre($prefeid=null,$buss_id=null){
		if (!empty($prefeid)) {
			$sql="SELECT * FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss  WHERE pre.id='{$prefeid}';";
			$info = $this->query($sql);
		}
		else{
			// SELECT * FROM kl_buss buss LEFT JOIN kl_prefe pre on buss.bs_id=pre.buss WHERE pre.buss is not null AND "2016-04-17">=pre.beg_time AND "2016-04-17"<=pre.end_time
			$now=date('Y-m-d');
			$sql="SELECT * FROM kl_prefe pre where pre.buss='{$buss_id}'  AND '{$now}'>=pre.beg_time AND '{$now}'<=pre.end_time;";
			$info = $this->query($sql);
		}
		
		return $info;
	}

	//获取当前用户签到次数
	public function getSignnum($openid=null,$shopid=null){
		$sql="SELECT count(*) num FROM kl_user_sign sign where sign.user='{$openid}'  AND sign.buss='{$shopid}';";
		$info = $this->query($sql);
		
		return $info;
	}

	//插入收藏记录
	public function collect($openid=null,$shopid=null){
		$now=date('Y-m-d H:i:s');
		$sql="insert into  kl_user_buss values('{$openid}','{$shopid}','{$now}');";
		$info = $this->execute($sql);
		
		return $info;
	}
}